﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using MySql.Data.MySqlClient;
using SmallDataBase.DTO;
using SmallDataBase.MySQL;

namespace SmallDataBase
{
    public sealed class SQLHelper:IDisposable
    {
        #region event

        // 创建一个委托，返回类型为void，两个参数
        public delegate void WatchSQLHandler(string sql);
        // sql查看事件
        public event WatchSQLHandler WatchSQL;

        public void OnWatchSQL(string sql)
        {
            WatchSQLHandler handler = WatchSQL;
            if (handler != null) handler(sql);
        }

        #endregion

        #region private

        private ConfigDTO Config { get; set; }

        private ISQLBuilder SQLBuilder
        {
            get { return Config.GetSQLBuilder(); }
        }

        #endregion

        #region 构造析构函数

        private bool _disposed = false;
        public SQLHelper()
        {
            Config = ConfigDTO.CreateInstance();
        }
        public SQLHelper(ConfigDTO config)
        {
            Config = config;
        }

        ~SQLHelper()
        {
            Dispose(false);
        }
        public void Dispose()
        {
            Dispose(true);
        }
        private void Dispose(bool disposing)
        {
            if (_disposed)
            {
                if (_connection != null)
                {
                    Config = null;
                    _connection.Close();
                    _connection.Dispose();
                } 
            }
            else
            {
                _disposed = true;
            }
            
        }

        #endregion

        #region insert,delete,update,query
        /// <summary>
        /// 插入数据库
        /// </summary>
        /// <param name="obj">插入对象</param>
        public void Insert(object obj)
        {
            var r = new Reflection(obj);
            MetaDTO meta = r.GetMeta();
            ExecuteNonQuery(SQLBuilder.BuildInsertSQL(meta), SQLBuilder.BuildParameter(meta).ToArray());
        }
        /// <summary>
        /// 插入数据库,并初始化ID
        /// </summary>
        /// <param name="obj">插入对象</param>
        public void InsertAndInitID(object obj)
        {
            var r = new Reflection(obj);
            MetaDTO meta = r.GetMeta();
            var value = ExecuteScalar(SQLBuilder.BuildInsertSQLAndReturnLastInsertID(meta), SQLBuilder.BuildParameter(meta).ToArray());
            r.SetAutoIncrementIDValue(value);
        }
        public IList<T> QueryObjects<T>() where T : new()
        {
            var r = new Reflection(typeof(T));
            MetaDTO sourceMeat = r.GetMeta();
            string sql = SQLBuilder.BuildSeletSQL(sourceMeat);
            DataTable table = ExecuteDataTable(sql);
            if (table != null)
            {
                return (from DataRow row in table.Rows select Reflection.GetObject<T>(row,Config.DataBaseType)).ToList();
            }
            return null;
        }

        public IList<T> QueryObjects<T>(object queryObj) where T : new()
        {
            var r = new Reflection(queryObj);
            MetaDTO queryMeta = r.GetMeta();
            r = new Reflection(typeof(T));
            MetaDTO sourceMeat = r.GetMeta();
            string sql = SQLBuilder.BuildSeletSQL(sourceMeat, queryMeta);
            DataTable table = ExecuteDataTable(sql, SQLBuilder.BuildParameter(queryMeta).ToArray());
            if (table != null)
            {
                return (from DataRow row in table.Rows select Reflection.GetObject<T>(row,Config.DataBaseType)).ToList();
            }
            return null;
        }
        public IList<T> QueryObjects<T>(params SQLQueryGroupDTO[] queryObj) where T : new()
        {
           
           var r = new Reflection(typeof(T));
            MetaDTO sourceMeat = r.GetMeta();
            string sql = SQLBuilder.BuildSeletSQL(sourceMeat, queryObj);

            List<DbParameter> paramList = new List<DbParameter>();
            foreach (var groupDTO in queryObj)
            {
                paramList.AddRange(SQLBuilder.BuildParameter(groupDTO));
            }

            DataTable table = ExecuteDataTable(sql, paramList.ToArray());
            if (table != null)
            {
                return (from DataRow row in table.Rows select Reflection.GetObject<T>(row, Config.DataBaseType)).ToList();
            }
            return null;
        }

        public void Delete<T>(object queryObj)
        {
            var r = new Reflection(queryObj);
            MetaDTO queryMeta = r.GetMeta();
            r = new Reflection(typeof(T));
            MetaDTO sourceMeat = r.GetMeta();
            string sql = SQLBuilder.BuildDeleteSQL(sourceMeat, queryMeta);
            ExecuteNonQuery(sql, SQLBuilder.BuildParameter(queryMeta).ToArray());
        }
        public void Delete<T>(params SQLQueryGroupDTO[] queryObj)
        {
            var r = new Reflection(typeof(T));
            MetaDTO sourceMeat = r.GetMeta();
            string sql = SQLBuilder.BuildDeleteSQL(sourceMeat, queryObj);
            List<DbParameter> paramList = new List<DbParameter>();
            foreach (var groupDTO in queryObj)
            {
                paramList.AddRange(SQLBuilder.BuildParameter(groupDTO));
            }
            ExecuteNonQuery(sql, paramList.ToArray());
        }

        public  void Update(object obj)
        {
            var r = new Reflection(obj);
            MetaDTO sourceMeat = r.GetMeta();
            string sql = SQLBuilder.BuildUpdateSQL(sourceMeat);
            ExecuteNonQuery(sql, SQLBuilder.BuildParameter(sourceMeat).ToArray());
        }

        #endregion

        #region sql execute

        public int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, new DbParameter[] {});
        }

        public int ExecuteNonQuery(string sql, params DbParameter[] param)
        {
            OnWatchSQL(sql);
            DbConnection conn = GetConnection();
            DbCommand command = SQLBuilder.BuildCommand();
            command.Connection = conn;
            command.CommandText = sql;
            if (param != null)
            {
                command.Parameters.AddRange(param);
            }
            return command.ExecuteNonQuery();
        }

        public object ExecuteScalar(string sql, params DbParameter[] param)
        {
            OnWatchSQL(sql);
            DbConnection conn = GetConnection();
            DbCommand command = SQLBuilder.BuildCommand();
            command.Connection = conn;
            command.CommandText = sql;
            if (param != null)
            {
                command.Parameters.AddRange(param);
            }
            return command.ExecuteScalar();
        }

        public  DataTable ExecuteDataTable(string sql, params DbParameter[] param)
        {
            OnWatchSQL(sql);
            DbConnection conn = GetConnection();
            DbCommand command = SQLBuilder.BuildCommand();
            command.Connection = conn;
            command.CommandText = sql;
            if (param != null)
            {
                command.Parameters.AddRange(param);
            }
            DataAdapter adp = new MySqlDataAdapter(command as MySqlCommand);
            var ds = new DataSet();
            adp.Fill(ds);
            if (ds.Tables.Count != 0)
            {
                return ds.Tables[0];
            }
            return null;
        }

        private DbConnection _connection;
        private DbConnection GetConnection()
        {
            if (_connection == null)
            {
                _connection = SQLBuilder.BuildConnection(Config);
            }
            if (_connection.State != ConnectionState.Open)
            {
                _connection.Open();
            }
            return _connection;
        }
        #endregion

    }
}